# coding:utf-8
from contextlib import closing
from math import ceil
import sqlite3,os
from flask import Flask, render_template, g, request, session, jsonify, current_app, redirect, url_for
from datetime import timedelta
import time
from xpinyin import Pinyin
from flask.ext.admin import Admin, BaseView, expose
import flask.ext.login
print os.getcwd()
if os.path.exists(r'e:\web\flask\topshipping'):
    os.chdir(r'e:\web\flask\topshipping')
elif os.path.exists(r'l:\web\flask\topshipping'):
    os.chdir(r'l:\web\flask\topshipping')
elif os.path.exists(r'g:\web\flask\topshipping'):
    os.chdir(r'g:\web\flask\topshipping')


DATABASE = r'topshipping.db3'
DEBUG=True
SECRET_KEY='development key'
USERNAME='admin'
PASSWORD='default'
PERMANENT_SESSION_LIFETIME = timedelta(minutes=15),

app = Flask(__name__)
app.config.from_object(__name__)

#app.permanent_session_lifetime = timedelta(hours=2)


def init_db():
    with closing(connect_db()) as db:
        with app.open_resource('schema.sql') as f:
            db.cursor().executescript(f.read())
        db.commit()

def query_db(query, args=(), one=False):
    cur = g.db.execute(query, args)
    rv = [dict((cur.description[idx][0], value)
        for idx, value in enumerate(row)) for row in cur.fetchall()]
    return (rv[0] if rv else None) if one else rv

def connect_db():
    return sqlite3.connect(app.config['DATABASE'])

@app.before_request
def before_request():
    g.db = connect_db()
    g.db.execute("PRAGMA foreign_keys = ON")

@app.teardown_request
def teardown_request(exception):
    if hasattr(g, 'db'):
        g.db.close()


def hz2py(hz):
    p=Pinyin()
    return p.get_pinyin(hz, ' ')

def request_wants_json():
    best = request.accept_mimetypes\
    .best_match(['application/json', 'text/html'])
    return best == 'application/json' and\
           request.accept_mimetypes[best] >\
           request.accept_mimetypes['text/html']


def get_mostport(limit=50):
    return query_db("select unloadport as PortID,PortName,PortNameEn from voyage,port where voyage.unloadport=port.portid group by unloadport order by count(unloadport) desc limit %d"%limit)


@app.route('/')
def index():
    return render_template('index.html',countrys=get_countryscount(),lines=get_linescount())

@app.route('/sea',methods=['GET','POST'])
def sea():
#    shipcompanys=query_db("select shipcompanyid,shipcompanyshort from shipcompany")
#    loadport=''
#    unloadport=''
#    if request.method=="GET":
#        unloadport=request.form.get('unloadport',None)
#        unloadportid=request.args.get('unloadportid',None,type=int)
#        return render_template('sea.html',unloadport=unloadport,unloadportid=unloadportid)
#    if request.method=="POST":
    loadport=request.values.get('loadport',None)
    unloadport=request.values.get('unloadport',None)
    loadportid=request.values.get('loadportid',None,type=int)
    unloadportid=request.values.get('unloadportid',None,type=int)

#        print request.form
    return render_template('sea.html',loadport=loadport,unloadport=unloadport,loadportid=loadportid,unloadportid=unloadportid)
#    return render_template('sea.html')

@app.route('/fee',methods=['GET'])
def fee():
    if request.args.has_key('voyageid'):
        voyageid=request.args['voyageid']
        if not voyageid.isdigit():
            voyageid=0
        else:
            voyageid=int(voyageid)

        fees=query_db("select * from exfee where voyageid=?",[voyageid])
#        print fees
        return render_template('fee.html',fees=fees)

@app.route('/seaprice',methods=['GET'])
def seaprice():
#    session['MemberID'] = 0
    session.pop('MemberID',None)
    if session.get('MemberID'):

        sql_sea='select * from sea where ( memberid=0 or memberid is null ) and voyageid not in (select voyageid from sea where memberid=%d) union select * from sea where memberid=%d'%(session.get('MemberID'),session.get('MemberID'))
    else:

        sql_sea='select * from sea where (memberid=0 or memberid is null)'
#    print session.get('a')
#    session.pop('MemberID',None)

    if request.method=='GET':
#        print request
        sql="select * from (%s) where 1=1 "%sql_sea
#        page=1
        pc=25

#        if request.args.has_key('page'):
#            page=request.args['page']
#            if not page.isdigit():
#                page=1
#            else:
#                page=int(page)
        page=request.args.get('page',1,type=int)
#        print 'page:',page
#        if request.args.has_key('loadport'):
#            loadport=request.args['loadport']
#            if loadport:
#                sql+=" and loadportnameen='%s'"%loadport
#        loadport=request.args.get('loadport',None)
#        if loadport:
#            sql+=" and loadportnameen='%s'"%loadport
#        unloadport=request.args.get('unloadport',None)
#        if unloadport:
#            sql+=" and unloadportnameen='%s'"%unloadport
#
        loadportid=request.args.get('loadportid',None,type=int)
        loadport=request.args.get('loadport',None)
        if loadportid<>0 and loadport:
            sql+=" and loadport=%s"%loadportid
        unloadportid=request.args.get('unloadportid',None,type=int)
        unloadport=request.args.get('unloadport',None)
        if unloadportid<>0 and unloadport:
            sql+=" and unloadport=%s"%unloadportid
#        if request.args.has_key('shipcompanyid'):
#            shipcompanyid=request.args['shipcompanyid']
        shipcompanyid=request.args.get('shipcompanyid',None,type=int)
        shipcompany=request.args.get('shipcompany',None)
        if shipcompanyid<>0 and shipcompany:
            sql+=" and shipcompanyid=%s"%shipcompanyid
        lineid=request.args.get('lineid',None,type=int)
        line=request.args.get('line',None)
        if lineid<>0 and line:
            sql+=" and lineid=%s"%lineid

        cutoff=request.args.get('cutoff',None,type=int)
        if cutoff:
            sql+=" and cutoff=%s"%cutoff
        sail=request.args.get('sail',None,type=int)
        if sail:
            sql+=" and sail=%s"%sail


#        if request.args.has_key('memberid'):
#            memberid=request.args['memberid']
#            sql+=" and memberid=%s"%memberid
#        else:
#            sql+=" and memberid=1"

#        if request.args.has_key('alltime'):
#            sql+=" and vaildend>=date('now','localtime')"
#        else:
#        if request.args.has_key('vaildstart'):
#            vaildstart=request.args['vaildstart']
#        if request.args.has_key('vaildend'):
#            vaildend=request.args['vaildend']
        vaildstart=request.args.get('vaildstart',None)
        vaildend=request.args.get('vaildend',None)
#        print vaildstart,vaildend
        if vaildstart and vaildend:
#            sql+=" and (vaildend>=date('now','localtime') and (vaildstart<='%s' and vaildend>='%s'))"%(vaildend,vaildstart)
            sql+=" and  (vaildstart<='%s' and vaildend>='%s')"%(vaildend,vaildstart)
        elif vaildstart:
#            sql+=" and (vaildend>=date('now','localtime') and  vaildend>='%s')"%vaildstart
            sql+=" and  vaildend>='%s'"%vaildstart
        elif vaildend:
#            sql+=" and (vaildend>=date('now','localtime') and  vaildstart<='%s')"%vaildend
            sql+=" and  vaildstart<='%s'"%vaildend
        else:
            sql+=" and cutoffdate>date('now','localtime')"

        sql+=" order by voyageid desc"
        sqlrows="select count(*) as rows from (%s)"%sql
#        print sqlrows
        rows=query_db(sqlrows,one=True)['rows']
#        if rows%pc==0 and rows<>0:
#            pages=rows//pc
#        else:
#            pages=rows//pc+1
        pages=int(ceil(rows/float(pc)))
        if page<=0:
            page=1
        if page>pages:
            page=pages
        offset=pc*(int(page)-1)

        sql+=" limit %d offset %d"%(pc,offset)
        print sql
        voyages=query_db(sql)
        return render_template('seaprice.html',voyages=voyages,pages=pages,page=page,rows=rows,vaildstart=vaildstart,vaildend=vaildend)


@app.route('/json_port')
def json_port():
    term = request.args.get('term', '', type=str)
    sql="select portid as id,portname||'('||portnameen||')&nbsp;&nbsp;&nbsp;&nbsp;<span style=''float:right''>'||countryname||'('||ifnull(countrynameen,'')||')</span>' as label,portnameen as value from vcountryport where portnameen like '%%%s%%' limit 10"%term.upper()
#    sql="select PortID,PortName,PortNameEn,CountryName from vcountryport where portnameen like '%%%s%%' limit 10"%port.upper()
#    print sql
    r=query_db(sql)
#    print r
    return jsonify(result=r)

@app.route('/json_shipcompany')
def json_shipcompany():
    term = request.args.get('term', '', type=str)
    sql="select shipcompanyid as id,shipcompanyname||'&nbsp;&nbsp;&nbsp;&nbsp;<span style=''float:right''>'||shipcompanynameen||'</span>' as label,shipcompanynameen as value from shipcompany where shipcompanynameen like '%%%s%%' limit 10"%term.upper()

    r=query_db(sql)
    #    print r
    return jsonify(result=r)

@app.route('/json_line')
def json_line():
    term = request.args.get('term', '', type=str)
    sql="select lineid as id,linename||'&nbsp;&nbsp;&nbsp;&nbsp;<span style=''float:right''>'||upper(linenamepys)||'</span>' as label,linename as value from line where linenamepys like '%%%s%%' limit 10"%term.lower()
    r=query_db(sql)
    #    print r
    return jsonify(result=r)


@app.route('/_json_port')
def _json_port():
    ports=query_db("select PortID,PortName,PortNameEn,CountryName from vcountryport")
    surchargecates=query_db("select surchargecateid,surchargecateshort,surchargecatename from surchargecate order by surchargecateshort")
    moneys=query_db("select moneyid,money3code from money order by money3code desc")
    vessels=query_db("select vesselid,vesselname,vesselnameen from vessel")
    return jsonify(result=[ports,surchargecates,moneys,vessels])

@app.route('/batch', defaults={'batchid': 0},methods=['GET','POST'])
@app.route('/batch/<int:batchid>',methods=['GET','POST'])
def batch(batchid):
    print 'batchid:%s'%batchid
    shipagents=query_db("select shipagentid,shipagentshort from shipagent order by shipagentshort asc")
    bookings=query_db("select freightforwardid as BookingID,freightforwardshort as BookingShort from freightforward order by BookingShort asc")
    lines=query_db("select lineid,linename from line order by linenamepy asc")
    shipcompanys=query_db("select shipcompanyid,shipcompanyshort from shipcompany order by shipcompanyshort asc")
    piers=query_db("select pierid,piername from pier order by piernamepy asc")
    surchargecates=query_db("select surchargecateid,surchargecateshort,surchargecatename from surchargecate")
    moneys=query_db("select moneyid,money3code from money order by money3code desc")
    common_voyage=None
    exfees=[]

    if request.method=='GET' and batchid<>0:
        voyages=query_db("select * from sea where batchid=?",(batchid,))
        if voyages:
            common_voyage=voyages[0]
#            exfees=query_db("select * from exfee where voyageid=?",(common_voyage['VoyageID'],))
#            exfees=query_db("select * from exfee where voyageid in (select distinct voyageid from sea where batchid=?)",(batchid,))
            for per_v in voyages:
                per_e=query_db("select * from exfee where voyageid=?",(per_v['VoyageID'],))
                exfees.append(per_e)
#            print 'exfees:%s'%exfees
        return render_template('batch.html',batchid=batchid,shipagents=shipagents,shipcompanys=shipcompanys,lines=lines,piers=piers,bookings=bookings,voyages=voyages,common_voyage=common_voyage,exfees=exfees,surchargecates=surchargecates,moneys=moneys)


    if request.method=='POST':
#        try:
        submit=request.form.get('submit',None)
        print 'submit:%s'%submit
        if submit==u'删除批次':
            g.db.execute("delete from batch where batchid=?",(batchid,))
            g.db.commit()
            return redirect(url_for('batch'))

#        v_batchid=request.form.get('v_batchid',None,type=int)
        v_vesselid=request.form.get('v_vesselid',None,type=int)
        v_voyagenumbername=request.form.get('v_voyagenumbername',None)
        v_loadport=request.form.get('v_loadport',None,type=int)
        v_shipcompany=request.form.get('v_shipcompany',None,type=int)
        v_line=request.form.get('v_line',None,type=int)
        v_shipagent=request.form.get('v_shipagent',None,type=int)
        v_booking=request.form.get('v_booking',None,type=int)
        v_cutoffsail=request.form.get('v_cutoffsail',None)
        v_bookinginfo=request.form.get('v_bookinginfo',None)
        if v_cutoffsail:
            v_cutoff,v_sail=v_cutoffsail.split('/')
        else:
            v_cutoff=None
            v_sail=None

        v_cutoffdate=request.form.get('v_cutoffdate',None)
        v_saildate=request.form.get('v_saildate',None)
        v_vaildstart=request.form.get('v_vaildstart',None)
        v_vaildend=request.form.get('v_vaildend',None)
        v_pier=request.form.get('v_pier',None,type=int)
        v_batchremarks=request.form.get('v_batchremarks',None)
        if submit==u'新增批次':batchid=0
        if batchid==0:
            g.db.execute("insert into batch(batchid,remarks) values(?,?)",(None,v_batchremarks))
            batchid=query_db("select last_insert_rowid() as BatchID",one=True)['BatchID']
        else:
            g.db.execute("update batch set remarks=? where batchid=?",(v_batchremarks,batchid))
            g.db.execute("delete from voyage where batchid=?",(batchid,))
        v_unloadport=request.form.getlist('v_unloadport',None)
        v_transitport=request.form.getlist('v_transitport',None)
        v_searange=request.form.getlist('v_searange',None)
        v_p20gp=request.form.getlist('v_p20gp')
        v_p40gp=request.form.getlist('v_p40gp')
        v_p40hq=request.form.getlist('v_p40hq')
        v_p45hq=request.form.getlist('v_p45hq')
        v_m20gp=request.form.getlist('v_m20gp')
        v_m40gp=request.form.getlist('v_m40gp')
        v_m40hq=request.form.getlist('v_m40hq')
        v_m45hq=request.form.getlist('v_m45hq')
        v_n20gp=request.form.getlist('v_n20gp')
        v_n40gp=request.form.getlist('v_n40gp')
        v_n40hq=request.form.getlist('v_n40hq')
        v_n45hq=request.form.getlist('v_n45hq')
        v_remarks=request.form.getlist('v_remarks')
#        temp_fee_name=request.form.getlist('temp_fee_name')
#        temp_fee_money=request.form.getlist('temp_fee_money')
#        temp_fee_20gp=request.form.getlist('temp_fee_20gp')
#        temp_fee_40gp=request.form.getlist('temp_fee_40gp')
#        temp_fee_40hq=request.form.getlist('temp_fee_40hq')
#        temp_fee_45hq=request.form.getlist('temp_fee_45hq')

        if v_vesselid==0:
            v_vesselid=None
            voyagenumber=query_db("select VoyageNumberID from voyagenumber where vesselid is null and voyagenumbername=? and voyagecate=?",(v_voyagenumbername,0),one=True)
        else:
            voyagenumber=query_db("select VoyageNumberID from voyagenumber where vesselid=? and voyagenumbername=? and voyagecate=?",(v_vesselid,v_voyagenumbername,0),one=True)
        if not voyagenumber:
            g.db.execute("insert into voyagenumber(vesselid,voyagenumbername,voyagecate) values(?,?,?)",(v_vesselid,v_voyagenumbername,0))
            voyagenumber=query_db("select last_insert_rowid() as VoyageNumberID",one=True)
        voyagenumberid=voyagenumber['VoyageNumberID']
#        temp_fee_rows=len(temp_fee_name)
        vrows=len(v_unloadport)
#        print 'vrows:%s,batchid:%s'%(vrows,batchid)
        if not vrows:
            g.db.execute("delete from batch where batchid=?",(batchid,))
            g.db.commit()
            return redirect(url_for('batch'))
#        print vrows,v_p20gp
        for x in xrange(vrows):
            if v_unloadport[x]=="0":
#                if x==0:
#                    g.db.execute("delete from batch where batchid=?",(batchid,))
#                    g.db.commit()
#                    return redirect(url_for('batch'))
                continue
            g.db.execute("update port set portused=1 where portid=?",(v_unloadport[x],))
            if v_transitport[x]<>"0":g.db.execute("update port set portused=1 where portid=?",(v_transitport[x],))
#            print x
#            if v_remarks[x]:
#                v_remarks[x]=v_remarks[x]+'\n'+remarks
#            else:
#                v_remarks[x]=remarks
#            print v_remarks[x]
            query_db("insert into voyage(loadport,unloadport,transitport,lineid,shipcompanyid,searange,pierid,cutoff,sail,cutoffdate,saildate,vaildstart,vaildend,remarks,shipagentid,bookingid,voyagenumberid,batchid,bookinginfo) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",(v_loadport,v_unloadport[x],v_transitport[x],v_line,v_shipcompany,v_searange[x],v_pier,v_cutoff,v_sail,v_cutoffdate,v_saildate,v_vaildstart,v_vaildend,v_remarks[x],v_shipagent,v_booking,voyagenumberid,batchid,v_bookinginfo))
            voyageid=query_db("select last_insert_rowid() as voyageid",one=True)['voyageid']
#            print 'voyageid:%s'%voyageid
            if v_p20gp[x]=='':v_p20gp[x]=None
            if v_m20gp[x]=='':v_m20gp[x]=None
            if v_n20gp[x]=='':v_n20gp[x]=None
            if v_p20gp[x] or v_m20gp[x] or v_n20gp[x]:
                query_db("insert into seaprice(voyageid,quoteboxid,publicprice,memberprice,netprice) values(?,?,?,?,?)",[voyageid,1,v_p20gp[x],v_m20gp[x],v_n20gp[x]])
            if v_p40gp[x]=='':v_p40gp[x]=None
            if v_m40gp[x]=='':v_m40gp[x]=None
            if v_n40gp[x]=='':v_n40gp[x]=None
            if v_p40gp[x] or v_m40gp[x] or v_n40gp[x]:
                query_db("insert into seaprice(voyageid,quoteboxid,publicprice,memberprice,netprice) values(?,?,?,?,?)",[voyageid,2,v_p40gp[x],v_m40gp[x],v_n40gp[x]])
            if v_p40hq[x]=='':v_p40hq[x]=None
            if v_m40hq[x]=='':v_m40hq[x]=None
            if v_n40hq[x]=='':v_n40hq[x]=None
            if v_p40hq[x] or v_m40hq[x] or v_n40hq[x]:
                query_db("insert into seaprice(voyageid,quoteboxid,publicprice,memberprice,netprice) values(?,?,?,?,?)",[voyageid,3,v_p40hq[x],v_m40hq[x],v_n40hq[x]])
            if v_p45hq[x]=='':v_p45hq[x]=None
            if v_m45hq[x]=='':v_m45hq[x]=None
            if v_n45hq[x]=='':v_n45hq[x]=None
            if v_p45hq[x] or v_m45hq[x] or v_n45hq[x]:
                query_db("insert into seaprice(voyageid,quoteboxid,publicprice,memberprice,netprice) values(?,?,?,?,?)",[voyageid,4,v_p45hq[x],v_m45hq[x],v_n45hq[x]])
            fee_name=request.form.getlist('fee_name_%s'%x)
            fee_money=request.form.getlist('fee_money_%s'%x)
            fee_20gp=request.form.getlist('fee_20gp_%s'%x)
            fee_40gp=request.form.getlist('fee_40gp_%s'%x)
            fee_40hq=request.form.getlist('fee_40hq_%s'%x)
            fee_45hq=request.form.getlist('fee_45hq_%s'%x)
            for i in xrange(len(fee_name)):

                if fee_20gp[i]:
                    query_db("insert into surcharge(voyageid,quoteboxid,surchargeprice,moneyid,surchargecateid) values(?,?,?,?,?)",[voyageid,1,fee_20gp[i],fee_money[i],fee_name[i]])
                if fee_40gp[i]:
                    query_db("insert into surcharge(voyageid,quoteboxid,surchargeprice,moneyid,surchargecateid) values(?,?,?,?,?)",[voyageid,2,fee_40gp[i],fee_money[i],fee_name[i]])
                if fee_40hq[i]:
                    query_db("insert into surcharge(voyageid,quoteboxid,surchargeprice,moneyid,surchargecateid) values(?,?,?,?,?)",[voyageid,3,fee_40hq[i],fee_money[i],fee_name[i]])
                if fee_45hq[i]:
                    query_db("insert into surcharge(voyageid,quoteboxid,surchargeprice,moneyid,surchargecateid) values(?,?,?,?,?)",[voyageid,4,fee_45hq[i],fee_money[i],fee_name[i]])

        g.db.commit()
        return redirect(url_for('batch',batchid=batchid))
#        except:
#            g.db.rollback()


    return render_template('batch.html',shipagents=shipagents,shipcompanys=shipcompanys,lines=lines,piers=piers,bookings=bookings,common_voyage=common_voyage)

@app.route('/voyage/<int:voyageid>',methods=['GET','POST'])
def voyage(voyageid):
    shipagents=query_db("select shipagentid,shipagentshort from shipagent")
    bookings=query_db("select freightforwardid as BookingID,freightforwardshort as BookingShort from freightforward")
    lines=query_db("select lineid,linename from line order by linenamepy asc")
    shipcompanys=query_db("select shipcompanyid,shipcompanyshort from shipcompany")
    piers=query_db("select pierid,piername from pier order by piernamepy asc")
    voyage=query_db("select * from sea where voyageid=?",(voyageid,),one=True)
#    fees=query_db("select * from fee where voyageid=?",(voyageid,))
    exfees=query_db("select * from exfee where voyageid=?",(voyageid,))
    surchargecates=query_db("select surchargecateid,surchargecateshort,surchargecatename from surchargecate")
    moneys=query_db("select moneyid,money3code from money order by money3code desc")

#    print voyage
#    print exfees
#    print surchargecates
    if request.method=='POST':
        act=request.form.get('submit',None)
        if act==u'删除':
#            query_db("delete from surcharge where voyageid=?",(voyageid,))
#            query_db("delete from seaprice where voyageid=?",(voyageid,))
            query_db("delete from voyage where voyageid=?",(voyageid,))
            g.db.commit()
            return redirect(url_for('sea'))
        if act==u'保存':
            request.form.get('submit',None)
        if act==u'查看本批运价':
            return redirect(url_for('batch',batchid=voyage['BatchID']))
        if act==u'新增一批运价':
            return redirect(url_for('batch'))


    return render_template('voyage.html',voyage=voyage,shipagents=shipagents,bookings=bookings,lines=lines,shipcompanys=shipcompanys,piers=piers,exfees=exfees,surchargecates=surchargecates,moneys=moneys)


def get_linescount(limit=-1):
    return query_db("select LineID,LineName,count(LineID) as PortsCount from vLinePort group by LineID order by count(LineID) desc limit %d"%limit)


def get_countryscount(limit=-1):
    return query_db("select CountryID,CountryName,count(CountryID) as PortsCount from vCountryPort group by CountryID order by count(CountryID) desc limit %d"%limit)


@app.route('/searchport', defaults={'searchstr': None},methods=['GET','POST'])
#@app.route('/searchport/<searchstr>',methods=['GET','POST'])
def searchport(searchstr):
#    ports=[]
#    if request.method=='POST':
    limit=50
    searchstr=request.form.get('searchstr',None)
    lineid=request.args.get('lineid',None,type=int)
    countryid=request.args.get('countryid',None,type=int)
    sql="select * from vPort where 1=1"
    if searchstr:
        sql+=" and (portname like '%%%s%%' or portnameen like '%%%s%%' or port5code like '%%%s%%')"%(searchstr,searchstr,searchstr)
#        ports=query_db("select * from vPort where portname like '%%%s%%' or portnameen like '%%%s%%' or countryname like '%%%s%%' or countrynameen like '%%%s%%' or port5code like '%%%s%%' limit 25"%(searchstr,searchstr,searchstr,searchstr,searchstr))
        if len(searchstr)>1:
            limit=-1
    else:
        limit=0
    if lineid:
        sql+=" and LineID=%d"%lineid
        limit=-1
    if countryid:
        sql+=" and CountryID=%d"%countryid
        limit=-1


    #    print ports
    sql+=" limit %d"%limit
    print sql
    s1=time.time()
    print s1
    ports=query_db(sql)
    s2=time.time()
    print s2-s1
    lines=get_linescount()
#    lines=[]
    s3=time.time()
    print s3-s2
    countrys=get_countryscount()
    s4=time.time()
    print s4-s3

    return render_template('searchport.html',ports=ports,searchstr=searchstr,lines=lines,lineid=lineid,countrys=countrys,countryid=countryid,mostports=get_mostport(100))

@app.route('/searchvessel', defaults={'searchstr': None},methods=['GET','POST'])
def searchvessel(searchstr):
    vessels=[]
    if request.method=='POST':
        searchstr=request.form.get('searchstr',None)
        vessels=query_db("select * from vessel where vesselname like '%%%s%%' or vesselnameen like '%%%s%%' or vesselimonumber like '%%%s%%' limit 25"%(searchstr,searchstr,searchstr))
        print vessels

    return render_template('searchvessel.html',vessels=vessels,searchstr=searchstr)

#def filterNone(v):
#    if not v:
#        return ''
#    else:
#        return v
#
#from jinja2 import Environment
#env = Environment()
#env.finalize = filterNone


@app.route('/login',methods=['POST'])
def login():
    session.permanent=True
    app.permanent_session_lifetime = timedelta(minutes=600)
    if request.method == 'POST':
        username= request.form.get('username',None)
        password= request.form.get('password',None)
        print username,password
        result={}
        if username=='top' and password=='topshipping':
            session['username'] = username
            session['logged_in'] = True
            result['bStatus']=True
            result['sInfo']='登陆成功'

            print True
        else:
            result['bStatus']=False
            result['sInfo']='登陆失败'
        if request_wants_json():
            return jsonify(result)

        return redirect(url_for('index'))

@app.route('/logout',methods=['POST'])
def logout():
    session.pop('username', None)
    session.pop('logged_in', None)
    if request_wants_json():
        return jsonify({})
    return redirect(url_for('index'))



# Add administrative views here
class MyView(BaseView):
    @expose('/')
    def index(self):
        return self.render('admin/index.html')

    def is_accessible(self):
        return login.current_user.is_authenticated()

if __name__ == '__main__':
    admin = Admin(app,name=u'托浦运价系统-管理界面')
    admin.add_view(MyView(name='Hello 1', endpoint='test1', category='Test'))
    admin.add_view(MyView(name='Hello 2', endpoint='test2', category='Test'))
    admin.add_view(MyView(name='Hello 3', endpoint='test3', category='Test'))
    app.run(host='0.0.0.0',port=5001)
